﻿using XDataBase.Demo.Data.Model;
using XLugia.Lib.XTool.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace XDataBase.Demo.Data.Script
{
    public class TDemoScript : BaseScript
    {
        public TDemoScript()
            : base("TDemo")
        { }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        public string getListData(TDemoModel searchModel = null)
        {
            StringBuilder sb = new StringBuilder();
            string searchText = "";
            //检索
            StringBuilder searchScript = new StringBuilder();
            searchScript.AppendLine(" WHERE ");
            searchScript.AppendLine(" 1 = 1 ");
            if (!String.IsNullOrEmpty(searchModel.fullTextSearch))
            {
                searchScript.AppendLine(" AND ( ");
                searchScript.AppendLine("     [Column1] LIKE N'%" + searchModel.fullTextSearch.quote() + "%'");
                searchScript.AppendLine("     OR [Column2] = '" + searchModel.fullTextSearch.quote() + "'");
                searchScript.AppendLine("     OR [Column3] = '" + searchModel.fullTextSearch.quote() + "'");
                searchScript.AppendLine("     OR [Column4] = '" + searchModel.fullTextSearch.quote() + "'");
                searchScript.AppendLine(" ) ");
            }
            searchText = searchScript.toString();

            //获取总数据量
            sb.AppendLine(" DECLARE @dataCount int ");
            sb.AppendLine("  ");
            sb.AppendLine(" SELECT ");
            sb.AppendLine(" @dataCount = COUNT(1) ");
            sb.AppendLine(" FROM [dbo].[TDemo]");
            sb.AppendLine(searchText);
            sb.Append(" ; ");

            //数据分页
            sb.AppendLine(" WITH TMP AS ( ");
            sb.AppendLine(" SELECT ");
            sb.AppendLine(" [ID] ");
            sb.AppendLine(" ,[Column1] ");
            sb.AppendLine(" ,[Column2] ");
            sb.AppendLine(" ,[Column3] ");
            sb.AppendLine(" ,[Column4] ");
            sb.AppendLine(" ,ROW_NUMBER() OVER(" + string.Format("ORDER BY {0} {1}",
                                                    searchModel.sortColumn,
                                                    searchModel.sortDirection == System.ComponentModel.ListSortDirection.Ascending ? "ASC" : "DESC")
                                                + ") AS [RowNumber] ");
            sb.AppendLine(" FROM [dbo].[TDemo]");
            sb.AppendLine(searchText);
            sb.AppendLine(" ) ");

            //获取分页数据
            sb.AppendLine(" SELECT ");
            sb.AppendLine(" A.[ID] ");
            sb.AppendLine(" ,A.[Column1] ");
            sb.AppendLine(" ,A.[Column2] ");
            sb.AppendLine(" ,A.[Column3] ");
            sb.AppendLine(" ,A.[Column4] ");
            sb.AppendLine(" ,@dataCount AS [DataCount] ");
            sb.AppendLine(" FROM TMP AS A ");
            if (searchModel != null)
            {
                sb.AppendLine(" WHERE ");
                sb.AppendLine(" 1 = 1 ");
                sb.AppendLine(string.Format(" AND A.[RowNumber] BETWEEN {0} AND {1} ", searchModel.startRowNumber, searchModel.endRowNumber));
            }

            if (searchModel != null && !Check.getIns().isEmpty(searchModel.sortColumn))
            {
                sb.AppendLine(string.Format("ORDER BY A.{0} {1}",
                    searchModel.sortColumn,
                    searchModel.sortDirection == System.ComponentModel.ListSortDirection.Ascending ? "ASC" : "DESC"));
            }
            return sb.ToString();
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        public string getData(TDemoModel model)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT ");
            sb.AppendLine(" [ID] ");
            sb.AppendLine(" ,[Column1] ");
            sb.AppendLine(" ,[Column2] ");
            sb.AppendLine(" ,[Column3] ");
            sb.AppendLine(" ,[Column4] ");
            sb.AppendLine(" FROM [dbo].[TDemo] ");
            sb.AppendLine(" WHERE ");
            sb.AppendLine(" [Column1] = @Column1 ");
            return sb.ToString();
        }

        /// <summary>
        /// 获取数据检索参数
        /// </summary>
        public SqlParameter[] getDataParameters(TDemoModel model)
        {
            SqlParameter[] parameters = new SqlParameter[1];

            SqlParameter parameter = new SqlParameter("@Column1", SqlDbType.NVarChar);
            parameter.Value = model.Column1;
            parameters[0] = parameter;

            return parameters;
        }

        /// <summary>
        /// 删除
        /// </summary>
        public string delete(TDemoModel model)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" DELETE FROM [dbo].[TDemo] ");
            sb.AppendLine(" WHERE ");
            sb.AppendLine(" [ID] = " + model.ID.sqlString());
            return sb.ToString();
        }

        /// <summary>
        /// 批量更新数据（新增和更新）
        /// </summary>
        public string updateMany(List<TDemoModel> models)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var model in models)
            {
                sb.AppendLine(update(model));
            }
            return sb.ToString();
        }

        /// <summary>
        /// 更新（新增和更新）
        /// </summary>
        public string update(TDemoModel model)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" IF ((SELECT COUNT(1) ");
            sb.AppendLine(" 	  FROM [dbo].[TDemo] ");
            sb.AppendLine(" 	  WHERE ");
            sb.AppendLine(" 	  [ID] = " + model.ID.sqlString() + ") <= 0) ");
            sb.AppendLine(" BEGIN ");

            sb.AppendLine(" INSERT INTO [dbo].[TDemo] ");
            sb.AppendLine(" ([Column1] ");
            sb.AppendLine(" ,[Column2] ");
            sb.AppendLine(" ,[Column3] ");
            sb.AppendLine(" ,[Column4]) ");
            sb.AppendLine(" VALUES ");
            sb.AppendLine(" (" + model.Column1.sqlString());
            sb.AppendLine(" ," + model.Column2.sqlString());
            sb.AppendLine(" ," + model.Column3.sqlString());
            sb.AppendLine(" ," + model.Column4.sqlString());
            sb.AppendLine(" ) ");

            sb.AppendLine(" END ");
            sb.AppendLine(" ELSE ");
            sb.AppendLine(" BEGIN ");

            sb.AppendLine(" UPDATE [dbo].[TDemo] ");
            sb.AppendLine(" SET  ");
            sb.AppendLine(" [Column1] = " + model.Column1.sqlString());
            sb.AppendLine(" ,[Column2] = " + model.Column2.sqlString());
            sb.AppendLine(" ,[Column3] = " + model.Column3.sqlString());
            sb.AppendLine(" ,[Column4] = " + model.Column4.sqlString());
            sb.AppendLine(" WHERE ");
            sb.AppendLine(" [ID] = " + model.ID.sqlString());

            sb.AppendLine(" End ");
            return sb.ToString();
        }
    }
}
